namespace IMPlant.Core.DataInput.Excel
{
    using System;
    using System.Data;
    using System.Data.OleDb;

    public static class ExcelReader
    {
        public static DataSet GetExcelData(string excelName)
        {
            OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";data source=" + excelName);
            connection.Open();
            DataTable oleDbSchemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            DataSet dataSet = new DataSet();
            foreach (DataRow row in oleDbSchemaTable.Rows)
            {
                OleDbCommand command = new OleDbCommand("Select * From [" + row[2].ToString().Trim() + "]", connection);
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, row[2].ToString().Trim());
            }
            connection.Close();
            return dataSet;
        }

        public static DataSet GetExcelDataHigh(string excelName)
        {
            OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OleDb.12.0;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";data source=" + excelName);
            connection.Open();
            DataTable oleDbSchemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            DataSet dataSet = new DataSet();
            foreach (DataRow row in oleDbSchemaTable.Rows)
            {
                if (row[2].ToString().Length <= 10)
                {
                    OleDbCommand command = new OleDbCommand("Select * From [" + row[2].ToString().Trim() + "]", connection);
                    adapter.SelectCommand = command;
                    adapter.Fill(dataSet, row[2].ToString().Trim());
                }
           
            }
            connection.Close();
            return dataSet;
        }
    }
}

